﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Threading.Tasks;
using System.Data.SqlClient;
using DAL;

namespace BLL
{
    public class ForBorrowRecord
    {
        public SqlDataAdapter SeeAllBorrow(string ReaderID="")
        {
            //查询所有借阅记录
            string sql = @"select Borrow.ReaderID,Reader.ReaderName,Book.BookName,Books.BookID,BorrowDate,GiveBackDate,ReturnDate 
                        from Borrow,Reader,Book,Books
                        where Borrow.BookID = Books.BookID 
                        and Borrow.ReaderID = Reader.ReaderID 
                        and Book.ID = Books.ID";
            if (ReaderID != "")
            {
                sql = sql + string.Format(" and Borrow.ReaderID = '{0}'", ReaderID.ToString());
            }
            SqlDataAdapter adapter = new SqlDataAdapter(sql, DBHelper.Connection);
            return adapter;
        }
        public SqlDataAdapter BindLoginReader()
        {
            string sql = string.Format(@"select Borrow.ReaderID,Reader.ReaderName,Book.BookName,Books.BookID,BorrowDate,GiveBackDate,ReturnDate  
                        from Borrow,Reader,Book,Books
                        where Borrow.BookID = Books.BookID 
                        and Borrow.ReaderID = Reader.ReaderID 
                        and Book.ID = Books.ID and Borrow.ReaderID = '{0}';",StaticStore.readerID);
            SqlDataAdapter adapter = new SqlDataAdapter(sql, DBHelper.Connection);
            return adapter;
        }
        public SqlDataAdapter SeeReaderBorrow(string ReaderID)
        {
            //按读者卡号查询借阅记录
            string sql = string.Format(@"select Borrow.ReaderID,Reader.ReaderName,Book.BookName,Books.BookID,BorrowDate,GiveBackDate,ReturnDate 
                        from Borrow,Reader,Book,Books
                        where Borrow.BookID = Books.BookID 
                        and Borrow.ReaderID = Reader.ReaderID 
                        and Book.ID = Books.ID and Borrow.ReaderID like '%{0}%'
                        order by ReturnDate", ReaderID);
            SqlDataAdapter adapter = new SqlDataAdapter(sql, DBHelper.Connection);
            return adapter;
        }
        public int UpdateBorrow(int BookID,string ReaderID)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("update Borrow set GiveBackDate = GETDATE() where BookID = {0} and readerID = '{1}' and GiveBackDate IS NULL",BookID, ReaderID);
            //执行
            int iRet = DBHelper.ExecuteNonQuery(sb.ToString());
            return iRet;
        }
        public int UpdateBooks(int StateID,int BookID)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@"update Books set StateID = '{0}' where BookID = '{1}'", StateID, BookID);
            int iRet = DBHelper.ExecuteNonQuery(sb.ToString());
            return iRet;
        }
        public int UpdateReader(string ReaderID)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@"update Reader set BorrowBook = BorrowBook + 1,ReturnBook = ReturnBook -1 where ReaderID = '{0}';", ReaderID);
            int iRet = DBHelper.ExecuteNonQuery(sb.ToString());
            return iRet;
        }
    }
}
